
**use "/Users/user/Downloads/POLCOMM City Council Data Raw.dta"

** dropping observations due to low video quality, absent members, or other factors that impede the coding of distraction
drop if drop==1

**collapsing constituent race variables into one constituent race variable 
gen race_const=1 if race_const_white==1
replace race_const=2 if race_const_black==1
replace race_const=3 if race_const_latino==1
replace race_const=4 if race_const_asian==1
replace race_const=5 if race_const_other==1
label define race_const1 1 "white" 2 "black" 3 "latino" 4 "asian" 5 "other"
label values race_const race_const1
tab race_const

**collapsing member race variables into one member race variable 
gen race_rep=1 if race_rep_white==1
replace race_rep=2 if race_rep_black==1
replace race_rep=3 if race_rep_hisp==1
replace race_rep=4 if race_rep_asian==1
label define race_rep1 1 "white" 2 "black" 3 "hispanic" 4 "asian"
label values race_rep race_rep1
tab race_rep

**generate a Black or White member race variable
gen black_white_rep=1 if race_rep_white==1
replace black_white_rep=0 if race_rep_black==1
label define black_white_rep1 1 "white" 0 "black"
label values black_white_rep black_white_rep1
tab black_white_rep

**generate a Black or White constituent race variable
gen black_white_const=1 if race_const_white==1
replace black_white_const=0 if race_const_black==1
label define black_white_const1 1 "white" 0 "black"
label values black_white_const black_white_const1
tab black_white_const

**generate a White or Non-White member race variable
gen white_nonwhite_rep=1 if race_rep==1
replace white_nonwhite_rep=0 if white_nonwhite_rep==.
label define white_nonwhite_rep1 1 "white" 0 "nonwhite"
label values white_nonwhite_rep white_nonwhite_rep1
tab white_nonwhite_rep

**collapsing city variables into one variable
gen city=1 if CityA==1
replace city=2 if CityB==1
replace city=3 if CityC==1
replace city=4 if CityD==1
replace city=5 if CityE==1
replace city=6 if CityF==1
label define city1 1 "City A" 2 "City B" 3 "City C" 4 "City D" 5 "City E" 6 "City F"
label values city city1
tab city

**create a new date variable
gen new_date=mdy(month, day, year)
tab new_date
format new_date %td

**create a variable in which sppech is centered on the removal of confederate statues in City F
gen conf_meeting=1 if new_date==21303 
replace conf_meeting=0 if conf_meeting==.
label define conf_meeting1 1 "Yes" 0 "No"
label values conf_meeting conf_meeting1 
tab conf_meeting

**creating the dependent variables based on 30 and 60 second time intervals

*distraction during 1st 30 seconds for constituents speaking for at least 30 seconds
gen new30sec=(distract30) if speech_total>=30
*distraction during 1st 60 seconds for constituents speaking for at least 1 minute
gen new1minute=(distract30+distract31_60) if speech_total>=60
*distraction during 61 -120 second interval for constituents speaking for at least 2 minutes
gen new2minutes=(distract61_90+distract91_120) if speech_total>=120
*distraction during 121 -180 second interval for constituents speaking for at least 3 minutes
gen new3minutes=(distract121_150+distract151_180) if speech_total>=180

**creating the dependent variables based time distracted during full 1, 2 and 3 minutes

*distraction during entire 60 seconds for constituents speaking for at least 1 minute
gen atleast60=(distract30+distract31_60) if speech_total>=60
*distraction during entire 90 seconds for constituents speaking for at least 1.5 minutes
gen atleast90= distract30+distract31_60+distract61_90 if speech_total>=90
*distraction during entire 120 seconds for constituents speaking for at least 2 minutes
gen atleast120= distract30+distract31_60+distract61_90+distract91_120 if speech_total>=120
*distraction during entire 150 seconds for constituents speaking for at least 2.5 minutes
gen atleast150= distract30+distract31_60+distract61_90+distract91_120+distract121_150 if speech_total>=150
*distraction during entire 180 seconds for constituents speaking for at least 3 minutes
gen atleast180= distract30+distract31_60+distract61_90+distract91_120+distract121_150+distract151_180 if speech_total>=180
*distraction during entire 180+ seconds for constituents speaking for more than 3 minutes
gen morethan180= distract30+distract31_60+distract61_90+distract91_120+distract121_150+distract151_180+distract180plus if speech_total>180

**creating alternative message order variable so that the highest value is 10 (one meeting had an unusally long public hearing in which more than 50 constituents spoke)
gen order2=1 if order==1
replace order2=2 if order==2
replace order2=3 if order==3
replace order2=4 if order==4
replace order2=5 if order==5
replace order2=6 if order==6
replace order2=7 if order==7
replace order2=8 if order==8
replace order2=9 if order==9
replace order2=10 if order>=10

**create a dummy variable for frequency of meeting participation (whether participant only participates once over a coures of 6 consecutive meetings)
gen freq_const2=1 if freq_const==1
replace freq_const2=2 if freq_const==2|freq_const==3|freq_const==4|freq_const==5
label define freq_const3 1 "once" 2 "more than once"
label values freq_const2 freq_const3
tab freq_const2

**create a dummy variable for ward congruence (member represents constituent's ward/district)
gen ward_rep2 =1 if ward_rep==1
replace ward_rep2=0 if ward_rep==0|ward_rep==2
label define ward_rep3 1 "congruence" 0 "none_unknown"
label values ward_rep2 ward_rep3
tab ward_rep2

**create a categorical variable for total speech time
gen cat_sptotal1=1 if speech_total<=60
replace cat_sptotal1=2 if speech_total>60 & speech_total<=120
replace cat_sptotal1=3 if speech_total>120 & speech_total<=180
replace cat_sptotal1=4 if speech_total>180 & speech_total<=240
replace cat_sptotal1=5 if speech_total>240 & speech_total<=300
replace cat_sptotal1=6 if speech_total>300
label define cat_sptotal 1 "60 seconds or less" 2 "from 61-120 seconds" 3 "from 121-180 seconds" 4 "from 181-240 seconds" 5 "from 241-300 seconds" 6 "more than 300 seconds"
label values cat_sptotal1 cat_sptotal
tab cat_sptotal1

**collapse categorical total speech variable into 4 categories and create new variable cat_sptotal2
recode cat_sptotal1 (5=4) (6=4), gen(cat_sptotal2)

**recode constituent ID variable to reconcile two different ID's for the same constituent 
recode const (327=290), gen(const1)

**create dummy variables for specific distracting activities

*talking
gen talk_dum=1 if talk>0
replace talk_dum=0 if talk_dum==.
label define talk_dum1 1 "talk during speech" 0 "no talking"
label values talk_dum talk_dum1
tab talk_dum

*writing something unrelated to constituent comments
gen write_dum=1 if write>0
replace write_dum=0 if write_dum==.
label define write_dum1 1 "write during speech" 0 "no writing"
label values write_dum write_dum1
tab write_dum

*leaving seat
gen leaveseat_dum=1 if leave_seat>0
replace leaveseat_dum=0 if leaveseat_dum==.
label define leaveseat_dum1 1 "leaves seat during speech" 0 "no leaving"
label values leaveseat_dum leaveseat_dum1
tab leaveseat_dum

*other general distracting activities
gen distractgen_dum=1 if distract_gen>0
replace distractgen_dum=0 if distractgen_dum==.
label define distractgen_dum1 1 "general distraction during speech" 0 "none"
label values distractgen_dum distractgen_dum1
tab distractgen_dum

*create continuous (percent) variable for specific distracting activities
gen talk_perc = talk/speech_total
gen write_perc = write/speech_total
gen distractgen_perc = distract_gen/speech_total
gen leaveseat_perc=leave_seat/speech_total

*save as "POLCOMM City Council Data Processed Final.dta"



